<?php

function print_error( $msg )
	{
		print <<<END
		<tr>
			<td colspan=5><font color=red><b>Error: </b></font>$msg</td>
			<td><font color=red><b>Rejected</b></font></td>
		</tr>

END;
	}

function getHeader( $exc, $data )
{
		// string
	
		$ind = $data['data'];
		if( $exc->sst[unicode][$ind] )
			return convertUnicodeString ($exc->sst['data'][$ind]);
		else
			return $exc->sst['data'][$ind];

}


function convertUnicodeString( $str )
{
	for( $i=0; $i<strlen($str)/2; $i++ )
	{
		$no = $i*2;
		$hi = ord( $str[$no+1] );
		$lo = $str[$no];
		
		if( $hi != 0 )
			continue;
		elseif( ! ctype_alnum( $lo ) )
			continue;
		else
			$result .= $lo;
	}
	
	return $result;
}

function uc2html($str) {
	$ret = '';
	for( $i=0; $i<strlen($str)/2; $i++ ) {
		$charcode = ord($str[$i*2])+256*ord($str[$i*2+1]);
		$ret .= '&#'.$charcode;
	}
	return $ret;
}



function get( $exc, $data )
{
	switch( $data['type'] )
	{
		// string
	case 0:
		$ind = $data['data'];
		if( $exc->sst[unicode][$ind] )
			return htmlcode2utf(uc2html($exc->sst['data'][$ind]));
		else
			return $exc->sst['data'][$ind];

		// integer
	case 1:
		return (integer) $data['data'];

		// float
	case 2:
		return (float) $data['data'];
        case 3:
		return gmdate("m-d-Y",$exc->xls2tstamp($data[data]));

	default:
		return '';
	}
}



function fatal($msg = '') {
	echo '[Fatal error]';
	if( strlen($msg) > 0 )
		echo ": $msg";
	echo "<br>\nScript terminated<br>\n";
	if( $f_opened) @fclose($fh);
	exit();
}


function getTableData ( $ws, $exc ) {
	
	
	global $excel_file, $db_table;
	global $db_host, $db_name, $db_user, $db_pass;
	
	$data = $ws['cell'];
	
echo <<<FORM

	<form action="" method="POST" name="db_export">
        <div style="width:790px;height:800px;overflow:scroll;">
	<table border="0" cellspacing="1" cellpadding="2" align="center" width=1500 bgcolor="#666666">
        
	<tr bgcolor="#f1f1f1">

FORM;

// Form fieldnames
	
if ( !$_POST['useheaders'] ) {
	for ( $j = 0; $j <= $ws['max_col']; $j++ ) {

		$field = "field" . $j;
						
		echo <<<HEADER

		<td style="display:none;">
		<input type="checkbox" name="fieldcheck[$j]" value="$j" checked title="Check to proceed this field">
		<input type="text" name="fieldname[$j]" value="$field" title="Field name">
		</td>

HEADER;
	}
}
else {
	for ( $j = 0; $j <= $ws['max_col']; $j++ ) {
		
		$field = getHeader ( $exc, $data[0][$j] );
			
		$field = ereg_replace ( "^[0-9]+", "", $field );
		
		if (empty ($field) )
			$field = "field" . $j;
		
		echo <<<HEADER

		<td>
		<input type="checkbox" name="fieldcheck[$j]" value="$j" checked title="Check to proceed this field">
		<input type="text" name="fieldname[$j]" value="$field" title="Field name">
		</td>

HEADER;
	}
}

	
	echo "</tr>";
	
            foreach( $data as $i => $row ) { // Output data and prepare SQL instructions


                    if ( $i == 0 && $_POST['useheaders'] )
                    continue;

                    echo "<tr bgcolor=\"#ffffff\">";

                    for ( $j = 0; $j <= $ws['max_col']; $j++ ) {
                            $_w     =   "";
                            $cell = get ( $exc, $row[$j] );
                            if($j == 13){
                                $_w =   "width='400'";
                            }

                            if($i <= 2){
                                echo "<td $_w style='background-color:green;color:white;'>$cell</td>";
                            }else{
                                echo "<td $_w>$cell</td>";
                            }

                    }

                    echo "</tr>";
                    $i++;
            }       
	
	if ( empty ( $db_table ) )
		$db_table = "Table1";
					
echo <<<FORM2
	
	</table><br>
        </div>
	<table align="center" width="390">	
	<tr><td></td><td><input type="hidden" name="excel_file" value="$excel_file">
	<input type="hidden" name="step" value="2">
	&nbsp;<input type="submit" name="submit" value="Output"></td></tr>
	</form>
        
	</table>
	<br>&nbsp;
FORM2;

} 



function prepareTableData ( $exc, $ws, $_photo_path='' ) {

	
	global $delivery,$mosConfig_absolute_path,$photos,$user_ids, $fname;
	getClass('product/entity/category');
        $cat    =   new Product_Enity_Category();                               
        include_once('includes/mark.gallery.php');        
        $files  =   scandir($_photo_path);    
            


	$data = $ws['cell'];
	$user_define_fieldname  =   array(2=>'product_name',
            3=>'sku',4=>'price_from',5=>'packing',6=>'cbm',7=>'weight',
            8=>'dimension',9=>'min_order_quantity',10=>'',11=>'payment',12=>'production_capacity',
            13=>'short_description',14=>'',15=>'attribute_set_id',16=>'user_id',17=>'group_id', 18=>'currency_id',
			19=>'packing_unit', 20=>'cbm_unit', 21=>'weight_unit', 22=>'minqty_unit', 23=>'tuber_cat_id', 24=>''
            );
        $now    =   date("Y-m-d H:i");
	foreach( $data as $i => $row ) { // Output data and prepare SQL instructions				
		if ( $i <= 2)
		continue;
		
		$SQL[$i] = "";
		
		for ( $j = 0; $j <= $ws['max_col']; $j++ ) {
		
            //if ( isset($fieldcheck[$j]) ) {
			if ( true ) {//process all columns
                            if($j == 10){
                                $delivery[$i]    =   addslashes ( get ( $exc, $row[$j] ) );
                            }                           

                            if($user_define_fieldname[$j]){
                                $SQL[$i] .= $user_define_fieldname[$j];
                                $SQL[$i] .= "=\"";
                                $SQL[$i] .= addslashes ( get ( $exc, $row[$j] ) );
                                $SQL[$i] .= "\"";
                                $SQL[$i] .= ",";
                            }
           }
		}
                $user_ids[$i]   =   intval(get ( $exc, $row[16] ));
                $photo_absolute_path    =    ProtectedDirAbsolutePath($user_ids[$i]);
                if(!((file_exists($photo_absolute_path."/product")) && is_dir($photo_absolute_path."/product"))){
                    mkdir($photo_absolute_path."/product",0777);
                }
                $photo_absolute_path    =    ProtectedDirRelativePath($user_ids[$i]) ;
                //proceed category
                $cid        =   addslashes ( get ( $exc, $row[14] ) );                
                $cat_str    =   $cat->getCatStr($cid).",";
                $SQL[$i]    .=  "category=\"".$cat_str."\",";
                

                //process owner group
                $gid        =   addslashes ( get ( $exc, $row[17] ) );                
                $group_str    =   $cat->getCatStr($gid).",";
                $SQL[$i]    .=  "owner_group=\"".$group_str."\",";
                
                //process sic
                $sic        =   get ( $exc, $row[24] );
                $sic_to_db = strtoupper($sic) == 'YES'?1:0;
                $SQL[$i]    .=  "sic=\"".$sic_to_db."\",";
                
                // process delivery time
				$dtime = addslashes ( get ( $exc, $row[10] ) );

				if (strpos($dtime, "-") > 0) {
					$darr = explode("-", $dtime);
					$SQL[$i]    .=  "delivery_time=\"".$darr[0]."\",";
					$SQL[$i]    .=  "delivery_time_to=\"".$darr[1]."\",";
				}
				else {
					$SQL[$i]    .=  "delivery_time=\"".$dtime."\",";
				}
                
                //proceed product photos
                $find_str   =    get ( $exc, $row[0] );
                $photos[$i] =   array();
                foreach ($files as $filename){
                    $_name_arr  =   explode(".", $filename);
                    $ext_str    =   str_replace($find_str, "", $_name_arr[0]);
                    $_photo     =   array();
                    $find_str   =   str_replace(" ", "", $find_str);
                    if($ext_str == null || ($ext_str && !is_numeric($ext_str))){
                        if (strpos($filename, $find_str) !== false){
                        	$now	=	date('ymdHis');
                        	$new_filename	=	$now."_".$filename;
							
                            $_photo['main_image'] =   $photo_absolute_path."/product/".$new_filename;
                            $src_img    =   $_photo_path."/".$filename;
                            $des_img    =   ProtectedDirAbsolutePath($user_ids[$i])."/product/thumb_200_".$new_filename;
                            if(createthumbnail(200, $src_img, $des_img, 90, 0,1)){
                                $_photo['thumb_image'] =   $photo_absolute_path."/product/thumb_200_".$new_filename;
                            }
                            array_push($photos[$i], $_photo);
                            copy($_photo_path."/".$filename, ltrim(ProtectedDirAbsolutePath($user_ids[$i]),"/")."/product/".$new_filename);
                            //copy($_photo_path."/thumb_200_".$filename, ltrim($photo_absolute_path,"/")."/product/thumb_200_".$filename);
                        }
                    }
                }
                
		$SQL[$i]    .=  "created_date=\"".$now."\",";
		$SQL[$i]    .=  "updated_date=\"".$now."\",";
		$SQL[$i]    .=  "excel_filename=\"".$fname."\",";		

		$SQL[$i] = rtrim($SQL[$i], ',');		
		$i++;
	}
        
	return $SQL;	
			
} 

function prepareUpdatedTableData ( $exc, $ws, $_photo_path='' ) {

	
	global $delivery,$mosConfig_absolute_path,$photos,$user_ids,$fname;
	getClass('product/entity/category');
        $cat    =   new Product_Enity_Category();                               
        include_once('includes/mark.gallery.php');        
        $files  =   scandir($_photo_path);    
            


	$data = $ws['cell'];
	$user_define_fieldname  =   array(2=>'product_name',
            3=>'sku',4=>'price_from',5=>'packing',6=>'cbm',7=>'weight',
            8=>'dimension',9=>'min_order_quantity',10=>'delivery_time',11=>'payment',12=>'production_capacity',
            13=>'short_description',14=>'category_id',15=>'attribute_set_id',16=>'user_id',17=>'group_id', 18=>'currency_id',
			19=>'packing_unit', 20=>'cbm_unit', 21=>'weight_unit', 22=>'minqty_unit'
            );
        $now    =   date("Y-m-d H:i");
	foreach( $data as $i => $row ) { // Output data and prepare SQL instructions				
		if ( $i <= 2)
		continue;
		
		$SQL[$i]['sql'] = "";
		$exl_user_id =	0;
		$exl_attributeset_id	=	0;
		$exl_group_id	=	0;
		$exl_category_id	=	0;
		$updated_product_arr	=	array();
		
		for ( $j = 0; $j <= $ws['max_col']; $j++ ) {
		
            //if ( isset($fieldcheck[$j]) ) {
			if ( true ) {//process all columns
							if($j == 14){
                            	$exl_category_id	=	addslashes ( get ( $exc, $row[$j] ) );
                            }
                            if($j == 15){
                            	$exl_attributeset_id	=	addslashes ( get ( $exc, $row[$j] ) );
                            }
                            
							if($j == 16){
                            	$exl_user_id	=	addslashes ( get ( $exc, $row[$j] ) );
                            }
                            
							if($j == 17){
                            	$exl_group_id	=	addslashes ( get ( $exc, $row[$j] ) );
                            }							
	           }
			}
			
		$existed	=		getRecord($exl_user_id,$exl_attributeset_id,$exl_group_id);

		foreach ($existed as $product){
			$product_id	=	$product->product_id;
			if(!in_array($product_id,$updated_product_arr))
			{
				array_push($updated_product_arr,$product_id);
			}
		}
		$updated_product_str	=	implode(",",$updated_product_arr);
		if($updated_product_str){			
			$cat_str    =   $cat->getCatStr($exl_category_id).",";			
			$SQL[$i]['sql']	=	"UPDATE cani_product set category='".$cat_str."', 
							cat_updated	='cat_updated,',
							excel_filename='".$fname."'							
							WHERE product_id IN (".$updated_product_str.") OR tracked_product_id IN (".$updated_product_str.")";
						
		}else{
			$SQL[$i]['error']	=	'Can not find product matched record number <b>'.($i+1).'</b> in file ';
		}
//                
		$i++;
	}        
	return $SQL;	
			
} 


function	getRecord($user_id,$attributeset_id,$group_id)
{
	global $database;
	$query	=	"SELECT product_id FROM cani_product WHERE user_id=".$user_id." AND attribute_set_id=".$attributeset_id." AND group_id=".$group_id;	
	$database->setQuery($query);
	return $database->loadObjectList();
}

function ProtectedDirRelativePath($user_id){
        global $mosConfig_absolute_path;
        if(!((file_exists($mosConfig_absolute_path."/images/sites/" . $user_id)) && is_dir($mosConfig_absolute_path."/images/sites/" . $user_id)))
        {
                mkdir($mosConfig_absolute_path."/images/sites/" . $user_id,0777);
        }
        $protectedDir = "/images/sites/" . $user_id . '/protected' ;
        return $protectedDir;
}

function ProtectedDirAbsolutePath($user_id){
        global $output_folder;

        $protectedDir = $output_folder . ProtectedDirRelativePath($user_id);

        if(!((file_exists($protectedDir)) && is_dir($protectedDir)))
        {
                mkdir_recursive($protectedDir);
        }        
        return $protectedDir;
}

function mkdir_recursive($targetDir){
	if(!((file_exists($targetDir)) && is_dir($targetDir)))
		{
			$dirs = explode('/',$targetDir);
			foreach($dirs as $dir)
			{
				$relPath .= $dir ."/"; 
				$absPath = $relPath;
				if(!((file_exists($absPath)) && is_dir($absPath)))
				{
					mkdir($absPath,0777);
				}
			}
		}
}


function htmlcode2utf($str)
{
    $utf    =   '';
    $str_arr    =   explode('&#', $str);
    foreach($str_arr as $code)
    {
        $utf    .=  code2utf($code);
    }
    return $utf;
}

function code2utf($number)
{
if ($number < 0)
return FALSE;

if ($number < 128)
    return chr($number);

// Removing / Replacing Windows Illegals Characters
       if ($number < 160)
       {
               if ($number==128) $number=8364;
           elseif ($number==129) $number=160; // (Rayo:) #129 using no relevant sign, thus, mapped to the saved-space #160
           elseif ($number==130) $number=8218;
           elseif ($number==131) $number=402;
           elseif ($number==132) $number=8222;
           elseif ($number==133) $number=8230;
           elseif ($number==134) $number=8224;
           elseif ($number==135) $number=8225;
           elseif ($number==136) $number=710;
           elseif ($number==137) $number=8240;
           elseif ($number==138) $number=352;
           elseif ($number==139) $number=8249;
           elseif ($number==140) $number=338;
           elseif ($number==141) $number=160; // (Rayo:) #129 using no relevant sign, thus, mapped to the saved-space #160
           elseif ($number==142) $number=381;
           elseif ($number==143) $number=160; // (Rayo:) #129 using no relevant sign, thus, mapped to the saved-space #160
           elseif ($number==144) $number=160; // (Rayo:) #129 using no relevant sign, thus, mapped to the saved-space #160
           elseif ($number==145) $number=8216;
           elseif ($number==146) $number=8217;
           elseif ($number==147) $number=8220;
           elseif ($number==148) $number=8221;
           elseif ($number==149) $number=8226;
           elseif ($number==150) $number=8211;
           elseif ($number==151) $number=8212;
           elseif ($number==152) $number=732;
           elseif ($number==153) $number=8482;
           elseif ($number==154) $number=353;
           elseif ($number==155) $number=8250;
           elseif ($number==156) $number=339;
           elseif ($number==157) $number=160; // (Rayo:) #129 using no relevant sign, thus, mapped to the saved-space #160
           elseif ($number==158) $number=382;
           elseif ($number==159) $number=376;
       } //if

if ($number < 2048)
return chr(($number >> 6) + 192) . chr(($number & 63) + 128);
if ($number < 65536)
return chr(($number >> 12) + 224) . chr((($number >> 6) & 63) + 128) . chr(($number & 63) + 128);
if ($number < 2097152)
return chr(($number >> 18) + 240) . chr((($number >> 12) & 63) + 128) . chr((($number >> 6) & 63) + 128) . chr(($number & 63) + 128);


return FALSE;
}

?>